#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel2User H5应用 - 后端API服务
功能：提供Excel数据上传、存储和查询的API接口
"""

from flask import Flask, request, jsonify, render_template, session, redirect, url_for
from flask_cors import CORS
import openpyxl
import sqlite3
import os
import hashlib
from datetime import datetime

app = Flask(__name__)
app.secret_key = 'excel2user_secret_key_2024'
CORS(app)

# 配置
UPLOAD_FOLDER = 'uploads'
DATABASE = 'student_data.db'

# 确保上传目录存在
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

def init_db():
    """初始化数据库"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 创建管理员表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS admin_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建学生数据表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS student_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            class_name TEXT NOT NULL,
            student_name TEXT NOT NULL,
            subject_data TEXT NOT NULL,
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 插入默认管理员账户
    admin_password = hashlib.md5('admin123'.encode()).hexdigest()
    cursor.execute('''
        INSERT OR IGNORE INTO admin_users (username, password_hash) 
        VALUES (?, ?)
    ''', ('admin', admin_password))
    
    conn.commit()
    conn.close()

def hash_password(password):
    """密码哈希"""
    return hashlib.md5(password.encode()).hexdigest()

@app.route('/')
def index():
    """首页 - 学生查询界面"""
    return render_template('index.html')

@app.route('/admin')
def admin_login():
    """管理员登录页面"""
    if 'admin_logged_in' in session:
        return redirect(url_for('admin_dashboard'))
    return render_template('admin_login.html')

@app.route('/admin/login', methods=['POST'])
def admin_login_post():
    """管理员登录处理"""
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')
    
    if not username or not password:
        return jsonify({'success': False, 'message': '用户名和密码不能为空'})
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    password_hash = hash_password(password)
    cursor.execute('SELECT id FROM admin_users WHERE username = ? AND password_hash = ?', 
                   (username, password_hash))
    user = cursor.fetchone()
    conn.close()
    
    if user:
        session['admin_logged_in'] = True
        session['admin_username'] = username
        return jsonify({'success': True, 'message': '登录成功'})
    else:
        return jsonify({'success': False, 'message': '用户名或密码错误'})

@app.route('/admin/logout')
def admin_logout():
    """管理员登出"""
    session.pop('admin_logged_in', None)
    session.pop('admin_username', None)
    return redirect(url_for('admin_login'))

@app.route('/admin/dashboard')
def admin_dashboard():
    """管理员后台页面"""
    if 'admin_logged_in' not in session:
        return redirect(url_for('admin_login'))
    return render_template('admin_dashboard.html')

@app.route('/api/upload_excel', methods=['POST'])
def upload_excel():
    """上传Excel文件并解析数据"""
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '请先登录'})
    
    if 'file' not in request.files:
        return jsonify({'success': False, 'message': '没有选择文件'})
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'success': False, 'message': '没有选择文件'})
    
    if file and file.filename.endswith(('.xlsx', '.xls')):
        try:
            # 使用openpyxl读取Excel文件
            workbook = openpyxl.load_workbook(file)
            worksheet = workbook.active
            
            # 获取所有行数据
            rows = list(worksheet.iter_rows(values_only=True))
            
            # 检查文件格式
            if len(rows) < 3:
                return jsonify({'success': False, 'message': 'Excel文件格式不正确，至少需要3行数据'})
            
            # 获取标题、字段名和数据
            title = rows[0][0] if rows[0][0] is not None else "未命名测试"
            headers = [cell for cell in rows[1] if cell is not None]
            
            # 清空现有数据
            conn = sqlite3.connect(DATABASE)
            cursor = conn.cursor()
            cursor.execute('DELETE FROM student_data')
            
            # 插入新数据
            processed_count = 0
            for i in range(2, len(rows)):
                row = rows[i]
                if not row[0] or not row[1]:  # 跳过班级或姓名为空的行
                    continue
                
                class_name = str(row[0]).strip()
                student_name = str(row[1]).strip()
                
                # 构建科目数据字典
                subject_data = {}
                for j, header in enumerate(headers[2:], start=2):
                    if j < len(row) and row[j] is not None:
                        subject_data[header] = str(row[j])
                
                cursor.execute('''
                    INSERT INTO student_data (class_name, student_name, subject_data)
                    VALUES (?, ?, ?)
                ''', (class_name, student_name, str(subject_data)))
                processed_count += 1
            
            conn.commit()
            conn.close()
            
            return jsonify({'success': True, 'message': f'Excel文件上传成功，共处理 {processed_count} 条学生数据'})
            
        except Exception as e:
            return jsonify({'success': False, 'message': f'文件处理失败: {str(e)}'})
    
    return jsonify({'success': False, 'message': '请上传Excel文件(.xlsx或.xls格式)'})

@app.route('/api/search_student', methods=['POST'])
def search_student():
    """搜索学生信息"""
    data = request.get_json()
    student_name = data.get('student_name', '').strip()
    
    if not student_name:
        return jsonify({'success': False, 'message': '请输入学生姓名'})
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT class_name, student_name, subject_data 
        FROM student_data 
        WHERE student_name LIKE ?
    ''', (f'%{student_name}%',))
    
    results = cursor.fetchall()
    conn.close()
    
    if results:
        students = []
        for row in results:
            class_name, name, subject_data_str = row
            try:
                subject_data = eval(subject_data_str)  # 将字符串转回字典
            except:
                subject_data = {}
            
            students.append({
                'class_name': class_name,
                'student_name': name,
                'subject_data': subject_data
            })
        
        return jsonify({'success': True, 'data': students})
    else:
        return jsonify({'success': False, 'message': '未找到该学生信息'})

@app.route('/api/get_class_students/<class_name>')
def get_class_students(class_name):
    """根据班级名获取学生名单（第三方API接口）"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 精确匹配班级名，支持多种匹配模式
    # 1. 完全匹配
    # 2. 末尾匹配（如输入"2班"匹配"2025级2班"）
    # 3. 避免部分匹配（如输入"2班"不匹配"12班"）
    
    # 首先尝试完全匹配
    cursor.execute('''
        SELECT DISTINCT student_name, class_name
        FROM student_data 
        WHERE class_name = ?
        ORDER BY student_name
    ''', (class_name,))
    
    results = cursor.fetchall()
    
    # 如果完全匹配没有结果，尝试智能匹配
    if not results:
        # 构建更精确的匹配模式
        if class_name.endswith('班'):
            # 如果输入的是"X班"格式，匹配"年级X班"或"X班"
            class_number = class_name[:-1]  # 去掉"班"字
            cursor.execute('''
                SELECT DISTINCT student_name, class_name
                FROM student_data 
                WHERE class_name = ? OR class_name LIKE ?
                ORDER BY student_name
            ''', (class_name, f'%级{class_name}'))
        else:
            # 其他情况使用前缀匹配
            cursor.execute('''
                SELECT DISTINCT student_name, class_name
                FROM student_data 
                WHERE class_name LIKE ?
                ORDER BY student_name
            ''', (f'{class_name}%',))
        
        results = cursor.fetchall()
    
    conn.close()
    
    if results:
        students = [{'student_name': row[0], 'class_name': row[1]} for row in results]
        return jsonify({
            'success': True,
            'class_name': class_name,
            'student_count': len(students),
            'students': students
        })
    else:
        return jsonify({
            'success': False,
            'message': f'未找到班级"{class_name}"的学生信息'
        })

@app.route('/api/get_all_classes')
def get_all_classes():
    """获取所有班级列表"""
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    cursor.execute('SELECT DISTINCT class_name FROM student_data ORDER BY class_name')
    results = cursor.fetchall()
    conn.close()
    
    classes = [row[0] for row in results]
    return jsonify({'success': True, 'classes': classes})

if __name__ == '__main__':
    init_db()
    app.run(debug=True, host='0.0.0.0', port=5000)